package com.ccrfid.rmc6.service.personnel;

import com.ccrfid.rmc6.appconfig.exception.pojo.AppException;
import com.ccrfid.rmc6.appconfig.exception.pojo.CustomErrors;
import com.ccrfid.rmc6.entity.device.CardActiveEntity;
import com.ccrfid.rmc6.entity.personnel.*;
import com.ccrfid.rmc6.entity.poi.PoiLocationEntity;
import com.ccrfid.rmc6.entity.sys.DictEntity;
import com.ccrfid.rmc6.entity.sys.LocationPersonnelActivityEntity;
import com.ccrfid.rmc6.entity.sys.OrgEntity;
import com.ccrfid.rmc6.lbservice3.pojo.msg.MsgLocate;
import com.ccrfid.rmc6.pojo.Constants;
import com.ccrfid.rmc6.pojo.dto.*;
import com.ccrfid.rmc6.pojo.dto.datapage.PersonnelLocationDto;
import com.ccrfid.rmc6.pojo.dto.personnel.*;
import com.ccrfid.rmc6.pojo.dto.visitroom.PersonelTypeCountDto;
import com.ccrfid.rmc6.pojo.param.SortParam;
import com.ccrfid.rmc6.repository.alarm.AlarmNotifyRecordVeiwRepository;
import com.ccrfid.rmc6.repository.device.CardActiveRepository;
import com.ccrfid.rmc6.repository.device.CardRepository;
import com.ccrfid.rmc6.repository.device.CardViewRepository;
import com.ccrfid.rmc6.repository.personnel.*;
import com.ccrfid.rmc6.repository.poi.PoiLocationRepository;
import com.ccrfid.rmc6.repository.search.SearchCriteria;
import com.ccrfid.rmc6.repository.sys.DictRepository;
import com.ccrfid.rmc6.repository.sys.LocationPersonnelActivityRepository;
import com.ccrfid.rmc6.service.sys.DictService;
import com.ccrfid.rmc6.service.sys.OrgService;
import com.ccrfid.rmc6.util.GeomUtil;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Polygon;
import org.hibernate.query.NativeQuery;
import org.hibernate.spatial.JTSGeometryType;
import org.hibernate.spatial.dialect.mysql.MySQLGeometryTypeDescriptor;
import org.hibernate.transform.Transformers;
import org.hibernate.type.IntegerType;
import org.hibernate.type.LongType;
import org.hibernate.type.StringType;
import org.hibernate.type.TimestampType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.transaction.Transactional;
import javax.ws.rs.core.Response;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import static org.springframework.data.jpa.repository.query.QueryUtils.toOrders;

@Service
@Transactional
public class PersonnelServiceImpl implements PersonnelService {
    private static final Logger logger = LoggerFactory.getLogger(PersonnelServiceImpl.class);
    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private PersonnelViewRepository personnelViewRepository;
    @Autowired
    private PersonnelRepository personnelRepository;
    @Autowired
    private PersonnelPhotoRepository personnelPhotoRepository;
    @Autowired
    private PersonnelPrisonerRepository personnelPrisonerRepository;
    @Autowired
    private PersonnelPrisonerViewRepository personnelPrisonerViewRepository;
    @Autowired
    private PersonnelMoveRepository personnelMoveRepository;
    @Autowired
    private CardRepository cardRepository;
    @Autowired
    private CardActiveRepository cardActiveRepository;
    @Autowired
    private DictService dictService;
    @Autowired
    private PoiLocationRepository poiLocationRepository;
    @Autowired
    private LocationPersonnelActivityRepository locationPersonnelActivityRepository;
    @Autowired
    private CardViewRepository cardViewRepository;
    @Autowired
    private OrgService orgService;
    @Autowired
    private AlarmNotifyRecordVeiwRepository alarmNotifyRecordVeiwRepository;
    @Autowired
    private DictRepository dictRepository;

    @Value("${rmc6.image.max_allowed_packet}")
    private int maxAllowedPacket;

    @Value("${locationtype}")
    private String appLocationType;

    @Value("${appversion}")
    private String appVersion;

    @Override
    public Page<PersonnelView> getPersonnelInfoPage(SearchCriteria criteria, Pageable pageable) {
        Page<PersonnelView> page = personnelViewRepository.search(criteria, pageable);
        return page;
    }

    @Override
    public List<PersonnelView> getPersonnelInfoList(SearchCriteria criteria, Sort sort) {
        return personnelViewRepository.search(criteria, sort);
    }

    @Override
    public Page<PersonnelPrisonerView> getPrisonerInfoPage(SearchCriteria criteria, Pageable pageable) {
        return personnelPrisonerViewRepository.search(criteria, pageable);
    }

    @Override
    public List<PersonnelPrisonerView> getPrisonerList(SearchCriteria criteria, Sort sort) {
        return personnelPrisonerViewRepository.search(criteria, sort);
    }

    @Override
    public List<PersonnelPrisonerView> getPrisonerList(Long accountOrgId, Integer enabled, Timestamp comeTimeStart,
                                                       Timestamp comeTimeStop, Integer prisonerType,
                                                       Integer onlineStatus, SortParam sortParam) {
        CriteriaBuilder builder = this.entityManager.getCriteriaBuilder();
        CriteriaQuery<PersonnelPrisonerView> pageCriteria = builder.createQuery(PersonnelPrisonerView.class);
        Root<PersonnelPrisonerView> pageRoot = pageCriteria.from(PersonnelPrisonerView.class);
        List<Predicate> predicateList = new ArrayList<>();
        if (accountOrgId != null) {
            List<Long> idList = orgService.getSubOrgIdList(accountOrgId);
            predicateList.add(pageRoot.get("orgId").in(idList));
        }
        if (enabled != null) {
            predicateList.add(builder.equal(pageRoot.get("enabled"), enabled));
        }
        if (comeTimeStart != null) {
            predicateList.add(builder.greaterThan(pageRoot.get("comeTime"), comeTimeStart));
        }
        if (comeTimeStop != null) {
            predicateList.add(builder.lessThan(pageRoot.get("comeTime"), comeTimeStop));
        }
        if (prisonerType != null) {
            switch (prisonerType) {
                case 150:   //死刑犯
                    predicateList.add(builder.equal(pageRoot.get("isDeathPrisoner"), 1));
                    break;
                case 151:   //重刑犯
                    predicateList.add(builder.equal(pageRoot.get("isMajorCriminalOffender"), 1));
                    break;
                case 152:   //危重病犯
                    predicateList.add(builder.equal(pageRoot.get("isInfectedPrisoner"), 1));
                    break;
                case 153:   //重点人员
                    predicateList.add(builder.equal(pageRoot.get("isFocusedPrisoner"), 1));
                    break;
            }
        }
        if (onlineStatus != null) {
            if (onlineStatus.equals(1)) {
                predicateList.add(builder.equal(pageRoot.get("onlineStatus"), onlineStatus));
            } else if (onlineStatus.equals(0)) {
                predicateList.add(
                        builder.or(builder.equal(pageRoot.get("onlineStatus"), onlineStatus),
                                builder.isNull(pageRoot.get("onlineStatus")))
                );
            }
        }
        List<PersonnelPrisonerView> list = this.entityManager.createQuery(
                pageCriteria.select(pageRoot)
                        .where(predicateList.toArray(new Predicate[predicateList.size()]))
                        .orderBy(toOrders(
                                Sort.by(
                                        (sortParam.getOrders() != null && sortParam.getOrders().size() > 0)
                                                ? sortParam.getOrders()
                                                : new ArrayList<>()
                                ), pageRoot, builder
                        ))).getResultList();
        return list;
    }

    @Override
    public PersonnelView getPersonnelDetailById(Long personnelId) {
        return personnelViewRepository.getById(personnelId);
    }

    @Override
    public PersonnelView getPersonnelDetailByCardNumber(Long cardNumber) {
        return personnelViewRepository.getByCardNumber(cardNumber);
    }

    @Override
    public PersonnelPrisonerView getPrisonerDetailById(Long personnelId) {
        return personnelPrisonerViewRepository.getById(personnelId);
    }

    @Override
    public List<PersonnelEntity> getPersonnelListByCardNumberNotNull() {
        return personnelRepository.getAllByCardNumberNotNull();
    }

    @Override
    public List<PersonnelEntity> getAllPersonnelsEnabled() {
        return personnelRepository.getAllByEnabled(Constants.ENABLE_STATUS.ENABLE.id);
    }

    @Override
    public List<PersonnelView> getPersonnelInfoByLocatorAddressNotNull() {
        return personnelViewRepository.getAllByLocatorAddressIsNotNull();
    }

    @Override
    public List<PersonnelView> getPersonnelInfoByLocatorAddress(Integer locatorAddress) {
        return personnelViewRepository.getAllByLocatorAddress(locatorAddress);
    }

    @Override
    public PersonnelEntity getPersonnelByCardNumber(Long cardNumber) {
        return personnelRepository.getByCardNumber(cardNumber);
    }

    @Override
    public PersonnelEntity getPersonnelById(Long personnelId) {
        return personnelRepository.getById(personnelId);
    }

    @Override
    public PersonnelEntity getPersonnelBySn(String personnelSn) {
        return personnelRepository.getByPersonnelSn(personnelSn);
    }

    @Override
    public PersonnelEntity createPersonnel(PersonnelEntity entity) throws AppException {
        checkCreateParam(entity);
        return personnelRepository.save(entity);
    }

    private void checkCreateParam(PersonnelEntity entity) throws AppException {
        if (entity.getCardNumber() != null) {
            if (personnelRepository.existsByCardNumber(entity.getCardNumber())) {
                throw new AppException(Response.Status.CONFLICT.getStatusCode(),
                        CustomErrors.card_number_conflict.getCode(), CustomErrors.card_number_conflict.getReason(), "");
            }
        }
        if (entity.getPersonnelSn() != null) {
            if (personnelRepository.existsByPersonnelSn(entity.getPersonnelSn())) {
                throw new AppException(Response.Status.CONFLICT.getStatusCode(),
                        CustomErrors.sn_conflict.getCode(), CustomErrors.sn_conflict.getReason(), "");
            }
        }
        if (entity.getIdNumber() != null) {
            if (personnelRepository.existsByIdNumberAndEnabled(entity.getIdNumber(), 1)) {
                throw new AppException(Response.Status.CONFLICT.getStatusCode(),
                        CustomErrors.id_number_conflict.getCode(), CustomErrors.id_number_conflict.getReason(), "");
            }
        }
    }

    @Override
    public PersonnelEntity createPrisoner(NewPrisonerDto dto) throws AppException {
        if (personnelPrisonerViewRepository.existsByUnifoNoAndPersonnelSnIsNull(dto.getUnifoNo())) {
            throw new AppException(Response.Status.CONFLICT.getStatusCode(),
                    CustomErrors.unifo_conflict.getCode(), CustomErrors.unifo_conflict.getReason(), "");
        }
        PersonnelEntity personnelEntity = new PersonnelEntity();
        personnelEntity.setPersonnelName(dto.getPersonnelName());
        personnelEntity.setNameAcronym(dto.getNameAcronym());
        personnelEntity.setEnabled(1);
        personnelEntity.setPersonnelType(101);
        personnelEntity = personnelRepository.save(personnelEntity);

        PersonnelPrisonerEntity prisonerEntity = new PersonnelPrisonerEntity();
        prisonerEntity.setPersonnelId(personnelEntity.getId());
        prisonerEntity.setUnifoNo(dto.getUnifoNo());
        personnelPrisonerRepository.save(prisonerEntity);

        return personnelEntity;
    }

    @Override
    public PersonnelEntity updatePersonnel(Long personnelId, PersonnelEntity entity) throws AppException {
        checkUpdateParam(personnelId, entity);
        return personnelRepository.save(entity);
    }

    private void checkUpdateParam(Long personnelId, PersonnelEntity entity) throws AppException {
        if (!personnelRepository.existsById(personnelId)) {
            throw new AppException(Response.Status.NOT_FOUND.getStatusCode(),
                    CustomErrors.resource_not_found.getCode(), CustomErrors.resource_not_found.getReason(), "");
        }
        if (!personnelId.equals(entity.getId())) {
            throw new AppException(Response.Status.BAD_REQUEST.getStatusCode(),
                    CustomErrors.different_args.getCode(), CustomErrors.different_args.getReason(), "");
        }
        if (entity.getCardNumber() != null) {
            if (personnelRepository.existsByCardNumberAndIdNot(entity.getCardNumber(), personnelId)) {
                throw new AppException(Response.Status.CONFLICT.getStatusCode(),
                        CustomErrors.card_number_conflict.getCode(), CustomErrors.card_number_conflict.getReason(), "");
            }
        }
        if (entity.getPersonnelSn() != null) {
            if (personnelRepository.existsByPersonnelSnAndIdNot(entity.getPersonnelSn(), personnelId)) {
                throw new AppException(Response.Status.CONFLICT.getStatusCode(),
                        CustomErrors.sn_conflict.getCode(), CustomErrors.sn_conflict.getReason(), "");
            }
        }
        if (entity.getIdNumber() != null) {
            if (personnelRepository.existsByIdNumberAndIdNotAndEnabled(entity.getIdNumber(), personnelId, 1)) {
                throw new AppException(Response.Status.CONFLICT.getStatusCode(),
                        CustomErrors.id_number_conflict.getCode(), CustomErrors.id_number_conflict.getReason(), "");
            }
        }
    }

    @Override
    public int deletePersonnels(List<Long> idList) {
        int count = 0;
        for (Long personnelId : idList) {
            if (personnelRepository.existsById(personnelId)) {
                personnelPhotoRepository.deleteByPersonnelId(personnelId);
                personnelPrisonerRepository.deleteByPersonnelId(personnelId);
                personnelRepository.deleteById(personnelId);
                count++;
            }
        }
        return count;
    }

    @Override
    public byte[] getPersonnelPhoto(Long personnelId) {
        PersonnelPhotoEntity photoEntity = personnelPhotoRepository.getByPersonnelId(personnelId);
        if (photoEntity != null) {
            return photoEntity.getImage();
        }
        return null;
    }

    @Override
    public Boolean updatePersonnelPhoto(Long personnelId, byte[] image) throws AppException {
        if (!personnelRepository.existsById(personnelId)) {
            throw new AppException(Response.Status.NOT_FOUND.getStatusCode(),
                    CustomErrors.resource_not_found.getCode(), CustomErrors.resource_not_found.getReason(), "");
        }
        if (image != null && image.length > maxAllowedPacket) {
            throw new AppException(Response.Status.REQUEST_ENTITY_TOO_LARGE.getStatusCode(),
                    CustomErrors.image_too_large.getCode(), CustomErrors.image_too_large.getReason(), "");
        }
        PersonnelPhotoEntity photoEntity = new PersonnelPhotoEntity();
        photoEntity.setPersonnelId(personnelId);
        photoEntity.setImage(image);
        personnelPhotoRepository.save(photoEntity);
        return true;
    }

    @Override
    public Boolean bindPersonnelCard(Long personnelId, Long cardNumber) throws AppException {
        if (!personnelRepository.existsById(personnelId)) {
            throw new AppException(Response.Status.NOT_FOUND.getStatusCode(),
                    CustomErrors.person_not_found.getCode(), CustomErrors.person_not_found.getReason(), "");
        }
        if (!cardRepository.existsByCardNumber(cardNumber)) {
            throw new AppException(Response.Status.NOT_FOUND.getStatusCode(),
                    CustomErrors.card_not_found.getCode(), CustomErrors.card_not_found.getReason(), "");
        }
        if (personnelRepository.existsByCardNumber(cardNumber)) {
            throw new AppException(Response.Status.CONFLICT.getStatusCode(),
                    CustomErrors.card_already_binded.getCode(), CustomErrors.card_already_binded.getReason(), "");
        }
        PersonnelEntity personnelEntity = personnelRepository.getById(personnelId);
        if (personnelEntity.getCardNumber() != null) {
            throw new AppException(Response.Status.CONFLICT.getStatusCode(),
                    CustomErrors.person_already_binded.getCode(), CustomErrors.person_already_binded.getReason(), "");
        }
        personnelEntity.setCardNumber(cardNumber);
        personnelRepository.save(personnelEntity);
        return true;
    }

    @Override
    public Boolean unBindPersonnelCard(Long personnelId, Long cardNumber) throws AppException {
        if (!personnelRepository.existsById(personnelId)) {
            throw new AppException(Response.Status.NOT_FOUND.getStatusCode(),
                    CustomErrors.person_not_found.getCode(), CustomErrors.person_not_found.getReason(), "");
        }
        if (!cardRepository.existsByCardNumber(cardNumber)) {
            throw new AppException(Response.Status.NOT_FOUND.getStatusCode(),
                    CustomErrors.card_not_found.getCode(), CustomErrors.card_not_found.getReason(), "");
        }
        PersonnelEntity personnelEntity = personnelRepository.getById(personnelId);
        if (personnelEntity.getCardNumber() == null || !personnelEntity.getCardNumber().equals(cardNumber)) {
            throw new AppException(Response.Status.BAD_REQUEST.getStatusCode(),
                    CustomErrors.different_person_card.getCode(), CustomErrors.different_person_card.getReason(), "");
        }
        personnelEntity.setCardNumber(null);
        personnelRepository.save(personnelEntity);
        return true;
    }

    @Override
    public Long countByIdListAndPersonnelType(Set<Long> idList, Integer personnelType) {
        List<Integer> typeList = dictService.getDictAndSubDictIdListById(personnelType);
        return personnelRepository.countAllByIdInAndPersonnelTypeInAndEnabled(idList, typeList, 1);
    }

    @Override
    public PersonnelMoveEntity recordPersonnelMove(Long personnelId, MsgLocate msgLocate) {
        PersonnelMoveEntity personnelMoveEntity = new PersonnelMoveEntity();
        personnelMoveEntity.setPersonnelId(personnelId);
        personnelMoveEntity.setCardNumber(msgLocate.getCardNumber());
        personnelMoveEntity.setFrontLocatorAddress(msgLocate.getFrontLocator());
        personnelMoveEntity.setFrontX(msgLocate.getFrontPos() != null ? msgLocate.getFrontPos().getX() : null);
        personnelMoveEntity.setFrontY(msgLocate.getFrontPos() != null ? msgLocate.getFrontPos().getY() : null);
        personnelMoveEntity.setFrontFloorNumber(msgLocate.getFrontFloorNumber());
        personnelMoveEntity.setCurrentLocatorAddress(msgLocate.getCurrentLocator());
        personnelMoveEntity.setCurrentX(msgLocate.getCurrentPos() != null ? msgLocate.getCurrentPos().getX() : null);
        personnelMoveEntity.setCurrentY(msgLocate.getCurrentPos() != null ? msgLocate.getCurrentPos().getY() : null);
        personnelMoveEntity.setCurrentFloorNumber(msgLocate.getCurrentFloorNumber());
        personnelMoveEntity.setRecordTime(new Timestamp(msgLocate.getTime()));
        return personnelMoveRepository.save(personnelMoveEntity);
    }

    @Override
    public Boolean existsByPersonnelId(Long personnelId) {
        return personnelRepository.existsById(personnelId);
    }

    @Override
    public List<LocatorPersonnelCount> getPersonnelMap(Integer personnelType, Integer floorNumber) {
        String sql = "";
        List<LocatorPersonnelCount> list;
        if (floorNumber == null) {
            sql = "SELECT a.locator_address as locatorAddress, a.pos, any_value(a.floor_number) as floorNumber, count(*) as personnelCount " +
                    " FROM personnel p " +
                    " left join v_card_active a on p.card_number = a.card_number " +
                    " where p.is_enabled = 1 and a.pos is not null and p.personnel_type in :types " +
                    " group by a.pos, a.locator_address";
            list = entityManager.createNativeQuery(sql)
                    .setParameter("types", dictService.getDictAndSubDictIdListById(personnelType))
                    .unwrap(NativeQuery.class)
                    .addScalar("locatorAddress", IntegerType.INSTANCE)
                    .addScalar("pos", new JTSGeometryType(MySQLGeometryTypeDescriptor.INSTANCE))
                    .addScalar("floorNumber", IntegerType.INSTANCE)
                    .addScalar("personnelCount", IntegerType.INSTANCE)
                    .setResultTransformer(Transformers.aliasToBean(LocatorPersonnelCount.class))
                    .list();
        } else {
            sql = "SELECT a.locator_address as locatorAddress, a.pos, a.floor_number as floorNumber, count(*) as personnelCount " +
                    " FROM personnel p " +
                    " left join v_card_active a on p.card_number = a.card_number " +
                    " where p.is_enabled = 1 and a.floor_number = :fn and a.pos is not null and p.personnel_type in :types " +
                    " group by a.pos, a.locator_address";
            list = entityManager.createNativeQuery(sql)
                    .setParameter("fn", floorNumber)
                    .setParameter("types", dictService.getDictAndSubDictIdListById(personnelType))
                    .unwrap(NativeQuery.class)
                    .addScalar("locatorAddress", IntegerType.INSTANCE)
                    .addScalar("pos", new JTSGeometryType(MySQLGeometryTypeDescriptor.INSTANCE))
                    .addScalar("floorNumber", IntegerType.INSTANCE)
                    .addScalar("personnelCount", IntegerType.INSTANCE)
                    .setResultTransformer(Transformers.aliasToBean(LocatorPersonnelCount.class))
                    .list();
        }
        return list;
    }

    @Override
    public List<LocatorPersonnelCount> getPrisonerMap(Integer prisonerType, Integer floorNumber) {
        String sql = "";
        List<LocatorPersonnelCount> list = null;
        if (floorNumber != null) {
            sql = "SELECT p.locator_address as locatorAddress, p.pos, p.floor_number as floorNumber, count(*) as personnelCount " +
                    " FROM v_personnel_prisoner p " +
                    " where p.is_enabled = 1 AND p.pos is not null and p.floor_number = :fn ";
            switch (prisonerType) {
                case 150:
                    sql += " and p.is_death_prisoner = 1 ";
                    break;
                case 151:
                    sql += " and p.is_major_criminal_offender = 1 ";
                    break;
                case 152:
                    sql += " and p.is_infected_prisoner = 1 ";
                    break;
                case 153:
                    sql += " and p.is_focused_prisoner = 1 ";
                    break;
            }
            sql += " group by p.pos, p.locator_address";

            list = entityManager.createNativeQuery(sql)
                    .setParameter("fn", floorNumber)
                    .unwrap(NativeQuery.class)
                    .addScalar("locatorAddress", IntegerType.INSTANCE)
                    .addScalar("pos", new JTSGeometryType(MySQLGeometryTypeDescriptor.INSTANCE))
                    .addScalar("floorNumber", IntegerType.INSTANCE)
                    .addScalar("personnelCount", IntegerType.INSTANCE)
                    .setResultTransformer(Transformers.aliasToBean(LocatorPersonnelCount.class))
                    .list();
        } else {
            sql = "SELECT p.locator_address as locatorAddress, p.pos, any_value(p.floor_number) as floorNumber, count(*) as personnelCount " +
                    " FROM v_personnel_prisoner p " +
                    " where p.is_enabled = 1 AND p.pos is not null  ";
            switch (prisonerType) {
                case 150:
                    sql += " and p.is_death_prisoner = 1 ";
                    break;
                case 151:
                    sql += " and p.is_major_criminal_offender = 1 ";
                    break;
                case 152:
                    sql += " and p.is_infected_prisoner = 1 ";
                    break;
                case 153:
                    sql += " and p.is_focused_prisoner = 1 ";
                    break;
            }
            sql += " group by p.pos, p.locator_address";

            list = entityManager.createNativeQuery(sql)
                    .unwrap(NativeQuery.class)
                    .addScalar("locatorAddress", IntegerType.INSTANCE)
                    .addScalar("pos", new JTSGeometryType(MySQLGeometryTypeDescriptor.INSTANCE))
                    .addScalar("floorNumber", IntegerType.INSTANCE)
                    .addScalar("personnelCount", IntegerType.INSTANCE)
                    .setResultTransformer(Transformers.aliasToBean(LocatorPersonnelCount.class))
                    .list();
        }
        return list;
    }

    @Override
    public PositionDto getPersonnelPosition(Long personnelId) {
        PersonnelView personnelView = personnelViewRepository.getById(personnelId);
        if (personnelView != null) {
            if (personnelView.getPos() != null) {
                PositionDto positionDto = new PositionDto();
                positionDto.setLocatorAddress(personnelView.getLocatorAddress());
                positionDto.setFloorNumber(personnelView.getFloorNumber());
                positionDto.setPos(personnelView.getPos());
                positionDto.setLastLocateTime(personnelView.getLastLocateTime());
                PoiLocationEntity locationEntity =
                        poiLocationRepository.get3LevelLocationContainsPoint(personnelView.getFloorNumber(), personnelView.getPos());
                if (locationEntity != null) {
                    positionDto.setLocationId(locationEntity.getId());
                    positionDto.setLocationName(locationEntity.getLocationName());
                }
                return positionDto;
            }
        }
        return null;
    }

    @Override
    public PoiLocationEntity getPersonnel3LevelLocation(Long personnelId) {
        PersonnelView personnelView = personnelViewRepository.getById(personnelId);
        if (personnelView != null && personnelView.getPos() != null) {
            PoiLocationEntity locationEntity =
                    poiLocationRepository.get3LevelLocationContainsPoint(personnelView.getFloorNumber(), personnelView.getPos());
            return locationEntity;
        } else {
            return null;
        }
    }

    @Override
    public List<MoveLineDto> getPersonnelMoveLine(Long personnelId, Timestamp startTime, Timestamp endTime) {
        String sql = "SELECT m.id, m.personnel_id, m.card_number, m.current_locator_address as locator_address, " +
                " m.current_floor_number as floor_number, point(m.current_x, m.current_y) as pos, m.record_time " +
                " FROM personnel_move m " +
                " where m.personnel_id = :perid and (m.record_time between :startTime and :endTime)";
        List<MoveLineDto> list = entityManager.createNativeQuery(sql, MoveLineDto.class)
                .setParameter("perid", personnelId)
                .setParameter("startTime", startTime)
                .setParameter("endTime", endTime)
                .getResultList();

        return list;
    }

    @Override
    public PersonnelLocationActivityDto getLevel3LocationActivity(Long trackId) {
        PersonnelMoveEntity moveEntity = personnelMoveRepository.getById(trackId);
        if (moveEntity != null && moveEntity.getCurrentX() != null && moveEntity.getCurrentY() != null) {
            Point position = GeomUtil.INSTANCE.createPoint(moveEntity.getCurrentX(), moveEntity.getCurrentY());
            PersonnelEntity personnelEntity = personnelRepository.getById(moveEntity.getPersonnelId());
            PoiLocationEntity locationEntity =
                    poiLocationRepository.get3LevelLocationContainsPoint(moveEntity.getCurrentFloorNumber(), position);
            if (locationEntity != null && personnelEntity != null) {
                PersonnelLocationActivityDto dto = new PersonnelLocationActivityDto(locationEntity);
                List<LocationPersonnelActivityEntity> activityEntities =
                        locationPersonnelActivityRepository.getActivityEntity(
                                personnelEntity.getPersonnelType(), locationEntity.getLocationType(), moveEntity.getRecordTime());
                if (activityEntities.size() > 0) {
                    dto.setActivityName(activityEntities.get(0).getActivityTypeName());
                }
                return dto;
            } else {
                return null;
            }
        } else {
            return null;
        }
    }

    @Override
    public List<PersonnelStatisticDto> getPersonnelStatisticList() {
        List<PersonnelStatisticDto> list = new ArrayList<>();
        //民警
        {
            Long totalCount = personnelViewRepository.countByEnabledAndPersonnelType(1, Constants.PERSONNEL_TYPE.POLICE.id);
            Long actualCount = personnelViewRepository.countByEnabledAndPersonnelTypeAndOnlineStatus(1, Constants.PERSONNEL_TYPE.POLICE.id, 1);
//            Long offlineCount = personnelViewRepository.countByEnabledAndPersonnelTypeAndOnlineStatus(1, Constants.PERSONNEL_TYPE.POLICE.id, 0);
            Long offlineCount = totalCount - actualCount;
            list.add(new PersonnelStatisticDto(Constants.PERSONNEL_TYPE.POLICE.id, Constants.PERSONNEL_TYPE.POLICE.name, totalCount, actualCount, offlineCount));
        }
        //文职
        {
            Long totalCount = personnelViewRepository.countByEnabledAndPersonnelType(1, Constants.PERSONNEL_TYPE.WEN_ZHI.id);
            Long actualCount = personnelViewRepository.countByEnabledAndPersonnelTypeAndOnlineStatus(1, Constants.PERSONNEL_TYPE.WEN_ZHI.id, 1);
//            Long offlineCount = personnelViewRepository.countByEnabledAndPersonnelTypeAndOnlineStatus(1, Constants.PERSONNEL_TYPE.WEN_ZHI.id, 0);
            Long offlineCount = totalCount - actualCount;
            list.add(new PersonnelStatisticDto(Constants.PERSONNEL_TYPE.WEN_ZHI.id, Constants.PERSONNEL_TYPE.WEN_ZHI.name, totalCount, actualCount, offlineCount));
        }
        //特勤
        {
            Long totalCount = personnelViewRepository.countByEnabledAndPersonnelType(1, Constants.PERSONNEL_TYPE.TE_QIN.id);
            Long actualCount = personnelViewRepository.countByEnabledAndPersonnelTypeAndOnlineStatus(1, Constants.PERSONNEL_TYPE.TE_QIN.id, 1);
//            Long offlineCount = personnelViewRepository.countByEnabledAndPersonnelTypeAndOnlineStatus(1, Constants.PERSONNEL_TYPE.TE_QIN.id, 0);
            Long offlineCount = totalCount - actualCount;
            list.add(new PersonnelStatisticDto(Constants.PERSONNEL_TYPE.TE_QIN.id, Constants.PERSONNEL_TYPE.TE_QIN.name, totalCount, actualCount, offlineCount));
        }
        //在押
        {
            Long totalCount = personnelViewRepository.countByEnabledAndPersonnelType(1, Constants.PERSONNEL_TYPE.PRISONER.id);
            Long actualCount = personnelViewRepository.countByEnabledAndPersonnelTypeAndOnlineStatus(1, Constants.PERSONNEL_TYPE.PRISONER.id, 1);
//            Long offlineCount = personnelViewRepository.countByEnabledAndPersonnelTypeAndOnlineStatus(1, Constants.PERSONNEL_TYPE.PRISONER.id, 0);
            Long offlineCount = totalCount - actualCount;
            list.add(new PersonnelStatisticDto(Constants.PERSONNEL_TYPE.PRISONER.id, Constants.PERSONNEL_TYPE.PRISONER.name, totalCount, actualCount, offlineCount));
        }
        //重点
        {
            Long totalCount = personnelPrisonerViewRepository.countByEnabledAndIsFocusedPrisoner(1, 1);
            Long actualCount = personnelPrisonerViewRepository.countByEnabledAndIsFocusedPrisonerAndOnlineStatus(1, 1, 1);
//            Long offlineCount = personnelPrisonerViewRepository.countByEnabledAndIsFocusedPrisonerAndOnlineStatus(1, 1, 0);
            Long offlineCount = totalCount - actualCount;
            list.add(new PersonnelStatisticDto(Constants.PERSONNEL_TYPE.PRISONER_FOCUSED.id, Constants.PERSONNEL_TYPE.PRISONER_FOCUSED.name, totalCount, actualCount, offlineCount));
        }
        //死刑
        {
            Long totalCount = personnelPrisonerViewRepository.countByEnabledAndIsDeathPrisoner(1, 1);
            Long actualCount = personnelPrisonerViewRepository.countByEnabledAndIsDeathPrisonerAndOnlineStatus(1, 1, 1);
//            Long offlineCount = personnelPrisonerViewRepository.countByEnabledAndIsDeathPrisonerAndOnlineStatus(1, 1, 0);
            Long offlineCount = totalCount - actualCount;
            list.add(new PersonnelStatisticDto(Constants.PERSONNEL_TYPE.PRISONER_DEATH.id, Constants.PERSONNEL_TYPE.PRISONER_DEATH.name, totalCount, actualCount, offlineCount));
        }
        //重型
        {
            Long totalCount = personnelPrisonerViewRepository.countByEnabledAndIsMajorCriminalOffender(1, 1);
            Long actualCount = personnelPrisonerViewRepository.countByEnabledAndIsMajorCriminalOffenderAndOnlineStatus(1, 1, 1);
//            Long offlineCount = personnelPrisonerViewRepository.countByEnabledAndIsMajorCriminalOffenderAndOnlineStatus(1, 1, 0);
            Long offlineCount = totalCount - actualCount;
            list.add(new PersonnelStatisticDto(Constants.PERSONNEL_TYPE.PRISONER_MAJOR_CRIMINAL.id, Constants.PERSONNEL_TYPE.PRISONER_MAJOR_CRIMINAL.name, totalCount, actualCount, offlineCount));
        }
        //传染病
        {
            Long totalCount = personnelPrisonerViewRepository.countByEnabledAndIsInfectedPrisoner(1, 1);
            Long actualCount = personnelPrisonerViewRepository.countByEnabledAndIsFocusedPrisonerAndOnlineStatus(1, 1, 1);
//            Long offlineCount = personnelPrisonerViewRepository.countByEnabledAndIsFocusedPrisonerAndOnlineStatus(1, 1, 0);
            Long offlineCount = totalCount - actualCount;
            list.add(new PersonnelStatisticDto(Constants.PERSONNEL_TYPE.PRISONER_INFECTED.id, Constants.PERSONNEL_TYPE.PRISONER_INFECTED.name, totalCount, actualCount, offlineCount));
        }
        return list;
    }

    @Override
    public PrisonerStatisticDto getPrisonerStatistic(Long accountOrgId) {
        int totalCount = getTotalPrisonerCount();
        int normalCount = getNormalPrisonerCount();
        int abnormalCount = totalCount - normalCount;
        int focalCount = getFocalPrisonerCount();
        boolean isRoot = null == orgService.getOrgInfoById(accountOrgId).getPid() ? true : false;
        return new PrisonerStatisticDto(totalCount, focalCount, normalCount, abnormalCount,
                getPrisonerAreaList(accountOrgId), getCardStatistic(), getPersonCountByType(), isRoot, isRoot ? getRcRecordList() : null);
    }

    private int getTotalPrisonerCount() {
        String sql = "SELECT count(*) FROM v_personnel_prisoner v " +
                " where v.is_enabled = 1 ";
        int count = ((Number) entityManager.createNativeQuery(sql).getSingleResult()).intValue();
        return count;
    }

    private int getNormalPrisonerCount() {
        String sql = "SELECT count(*) FROM v_personnel_prisoner v " +
                " where v.is_enabled = 1 and v.online_status = 1 ";
        int count = ((Number) entityManager.createNativeQuery(sql).getSingleResult()).intValue();
        return count;
    }

    private int getFocalPrisonerCount() {
        String sql = "SELECT count(*) FROM v_personnel_prisoner v " +
                " where v.is_enabled = 1 and v.is_focused_prisoner = 1";
        int count = ((Number) entityManager.createNativeQuery(sql).getSingleResult()).intValue();
        return count;
    }

    private List<PrisonerAreaDto> getPrisonerAreaList(Long accountOrgId) {
        List<PrisonerAreaDto> list = new ArrayList<>();
        List<OrgEntity> prisonAreaList = orgService.getAllPrisonAreasByOrgId(accountOrgId);
        for (OrgEntity orgEntity : prisonAreaList) {
            list.add(getPrisonerArea(orgEntity));
        }
        return list;
    }

    private PrisonerAreaDto getPrisonerArea(OrgEntity orgEntity) {
        List<Long> orgIdList = orgService.getSubOrgIdList(orgEntity.getId());
        String sql = "SELECT v.id as personnelId, v.personnel_name as personnelName, v.card_number as cardNumber, " +
                "  v.online_status as onlineStatus, v.is_focused_prisoner as isFocusedPrisoner, " +
                "  p.id as locationId, p.location_name as locationName, p.location_type as locationType, d.pid as locationTypePid " +
                " FROM v_personnel_prisoner v " +
                " left join (select * from poi_location where map_level = 3) p " +
                "  on st_contains(p.location_polygon, v.pos) and p.floor_number = v.floor_number " +
                " left join s_dict d on p.location_type = d.id " +
                " where v.is_enabled = 1 " +
                "  and v.org_id in :orgIdList";
        List<PrisonerDto> prisonerList = entityManager.createNativeQuery(sql)
                .setParameter("orgIdList", orgIdList)
                .unwrap(NativeQuery.class)
                .addScalar("personnelId", LongType.INSTANCE)
                .addScalar("personnelName", StringType.INSTANCE)
                .addScalar("cardNumber", LongType.INSTANCE)
                .addScalar("onlineStatus", IntegerType.INSTANCE)
                .addScalar("isFocusedPrisoner", IntegerType.INSTANCE)
                .addScalar("locationId", LongType.INSTANCE)
                .addScalar("locationName", StringType.INSTANCE)
                .addScalar("locationType", IntegerType.INSTANCE)
                .addScalar("locationTypePid", IntegerType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(PrisonerDto.class))
                .list();
        int jianQvCount = 0, gongChangCount = 0, yiWuCount = 0, yanGuanCount = 0, otherCount = 0, normalCount = 0, totalCount = 0, focalCount = 0;
        if (prisonerList != null) {
            totalCount = prisonerList.size();
            for (PrisonerDto prisonerDto : prisonerList) {
                if (prisonerDto.getOnlineStatus() != null && prisonerDto.getOnlineStatus().equals(1)) {
                    normalCount++;
                }
                if (prisonerDto.getIsFocusedPrisoner() != null && prisonerDto.getIsFocusedPrisoner().equals(1)) {
                    focalCount++;
                }
                if (Constants.LOCATION_TYPE.JianQv.id.equals(prisonerDto.getLocationType())
                        || Constants.LOCATION_TYPE.JianQv.id.equals(prisonerDto.getLocationTypePid())) {
                    jianQvCount++;
                } else if (Constants.LOCATION_TYPE.GongChang.id.equals(prisonerDto.getLocationType())
                        || Constants.LOCATION_TYPE.GongChang.id.equals(prisonerDto.getLocationTypePid())) {
                    gongChangCount++;
                } else if (Constants.LOCATION_TYPE.YiWu.id.equals(prisonerDto.getLocationType())
                        || Constants.LOCATION_TYPE.YiWu.id.equals(prisonerDto.getLocationTypePid())) {
                    yiWuCount++;
                } else if (Constants.LOCATION_TYPE.YanGuan.id.equals(prisonerDto.getLocationType())
                        || Constants.LOCATION_TYPE.YanGuan.id.equals(prisonerDto.getLocationTypePid())) {
                    yanGuanCount++;
                } else {
                    otherCount++;
                }
            }
        }
        List<AreaCategoryDto> categoryList = new ArrayList<>();
        categoryList.add(new AreaCategoryDto("监室", jianQvCount));
        categoryList.add(new AreaCategoryDto("车间", gongChangCount));
        categoryList.add(new AreaCategoryDto("医院", yiWuCount));
        categoryList.add(new AreaCategoryDto("严管禁闭", yanGuanCount));
        categoryList.add(new AreaCategoryDto("其他", otherCount));
        return new PrisonerAreaDto(orgEntity.getOrgName(), totalCount, focalCount, normalCount,
                totalCount - normalCount, categoryList);
    }

    //获取点名对象组点名统计
    private List<RollCallRecordDto> getRcRecordList() {
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Timestamp startTime = Timestamp.valueOf(dateFormat.format(new Date()) + " 00:00:00");
        String sql = "SELECT COUNT(*) as rollCallTimes,org_group_name as orgGroupName," +
                "SUM(attendance_count)+SUM(absence_count) as calledCount," +
                "sum(total_count)-SUM(attendance_count)-SUM(absence_count) as unCalledCount" +
                " FROM rc_record  WHERE rollcall_time >= :startTime GROUP BY org_group_name;";
        List<RollCallRecordDto> list = entityManager.createNativeQuery(sql)
                .setParameter("startTime", startTime)
                .unwrap(NativeQuery.class)
                .addScalar("orgGroupName", StringType.INSTANCE)
                .addScalar("rollCallTimes", IntegerType.INSTANCE)
                .addScalar("calledCount", LongType.INSTANCE)
                .addScalar("unCalledCount", LongType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(RollCallRecordDto.class))
                .getResultList();
        return list;
    }

    //获取异常标签数量详情
    private CardStatisticDto getCardStatistic() {
        int untreatedCount = untreatedCount();
        int lowpowerStatusCount = lowpowerStatusCount();
        int cutoffStatusCount = cutoffStatusCount();
        int offlineStatusCount = offlineStatusCount();
        int unKnowAreaCount = unKnowAreaCount();
        return new CardStatisticDto(untreatedCount, lowpowerStatusCount, cutoffStatusCount, offlineStatusCount, unKnowAreaCount);
    }

    private int untreatedCount() {
        String sql = "SELECT count(*) FROM v_card v left join v_card_active vc" +
                " on v.card_number = vc.card_number" +
                " where v.lowpower_status = 1 or v.cutoff_status = 1 or v.online_status = 0 or vc.pos is null ";

        int count = ((Number) entityManager.createNativeQuery(sql).getSingleResult()).intValue();
        return count;
    }

    private int lowpowerStatusCount() {
        String sql = "SELECT count(*) FROM  v_card v " +
                " where v.lowpower_status = 1 ";
        int count = ((Number) entityManager.createNativeQuery(sql).getSingleResult()).intValue();
        return count;
    }

    private int cutoffStatusCount() {
        String sql = "SELECT count(*) FROM v_card v " +
                " where v.cutoff_status = 1 ";
        int count = ((Number) entityManager.createNativeQuery(sql).getSingleResult()).intValue();
        return count;
    }

    private int offlineStatusCount() {
        String sql = "SELECT count(*) FROM v_card v " +
                " where v.online_status = 0 ";
        int count = ((Number) entityManager.createNativeQuery(sql).getSingleResult()).intValue();
        return count;
    }

    private int unKnowAreaCount() {
        String sql = "SELECT count(*) FROM v_card v left  join v_card_active vc " +
                "on v.card_number = vc.card_number WHERE vc.pos is null ";
        int count = ((Number) entityManager.createNativeQuery(sql).getSingleResult()).intValue();
        return count;
    }


    @Override
    public List<PersonnelPositionView> getPersonnelsInPolygon(Integer floorNumber, Polygon polygon) {
        if (floorNumber != null && polygon != null) {
            String sql = "select * from v_personnel_position p where p.floor_number = :fn and st_contains(:pg, p.pos)";
            List<PersonnelPositionView> list = entityManager.createNativeQuery(sql, PersonnelPositionView.class)
                    .setParameter("fn", floorNumber)
                    .setParameter("pg", polygon)
                    .getResultList();
            return list;
        } else {
            return new ArrayList<>();
        }
    }

    private PersonelTypeCountDto getPersonCountByType() {
        Long prisonerCount = personnelRepository.countAllByPersonnelTypeInAndEnabled(dictService.getDictAndSubDictIdListById(Constants.PERSONNEL_TYPE.PRISONER.id), 1);
        Long policeCount = personnelRepository.countAllByPersonnelTypeInAndEnabled(dictService.getDictAndSubDictIdListById(Constants.PERSONNEL_TYPE.POLICE.id), 1);
        Long visitorCount = personnelRepository.countAllByPersonnelTypeInAndEnabled(dictService.getDictAndSubDictIdListById(Constants.PERSONNEL_TYPE.VISITOR.id), 1);
        return new PersonelTypeCountDto(prisonerCount, policeCount, visitorCount);
    }

    @Override
    public PersonnelLocatorDto getPersonnelLocatorInfo(String personnelSn) throws AppException {
        PersonnelEntity personnelEntity = personnelRepository.getByPersonnelSn(personnelSn);
        if (personnelEntity != null) {
            PersonnelLocatorDto personnelLocatorDto = new PersonnelLocatorDto();
            personnelLocatorDto.setPersonnelId(personnelEntity.getId());
            personnelLocatorDto.setPersonnelSn(personnelSn);
            personnelLocatorDto.setPersonnelName(personnelEntity.getPersonnelName());
            personnelLocatorDto.setPersonnelType(personnelEntity.getPersonnelType());
            personnelLocatorDto.setCardNumber(personnelEntity.getCardNumber());
            if (personnelEntity.getCardNumber() != null) {
                CardActiveEntity cardActiveEntity = cardActiveRepository.getByCardNumber(personnelEntity.getCardNumber());
                if (cardActiveEntity != null) {
                    personnelLocatorDto.setLocatorAddress(cardActiveEntity.getLocatorAddress());
                    personnelLocatorDto.setFloorNumber(cardActiveEntity.getFloorNumber());
                    personnelLocatorDto.setLocateTime(cardActiveEntity.getUpdateTime());
                }
            }
            return personnelLocatorDto;
        } else {
            throw new AppException(Response.Status.NOT_FOUND.getStatusCode(),
                    CustomErrors.person_not_found.getCode(), CustomErrors.person_not_found.getReason(), "");
        }
    }

    @Override
    public List<PersonnelLocatorDto> getAllPersonnelLocatorInfo() {
        String sql = "select p.id as personnelId, p.personnel_sn as personnelSn, p.personnel_name as personnelName, " +
                "  p.personnel_type as personnelType, p.card_number as cardNumber, a.locator_address as locatorAddress, " +
                "  a.floor_number as floorNumber, a.update_time as locateTime " +
                " from personnel p " +
                " left join d_card_active a on p.card_number = a.card_number " +
                " where p.is_enabled = 1 ";
        List<PersonnelLocatorDto> list = entityManager.createNativeQuery(sql)
                .unwrap(NativeQuery.class)
                .addScalar("personnelId", LongType.INSTANCE)
                .addScalar("personnelSn", StringType.INSTANCE)
                .addScalar("personnelName", StringType.INSTANCE)
                .addScalar("personnelType", IntegerType.INSTANCE)
                .addScalar("cardNumber", LongType.INSTANCE)
                .addScalar("locatorAddress", IntegerType.INSTANCE)
                .addScalar("floorNumber", IntegerType.INSTANCE)
                .addScalar("locateTime", TimestampType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(PersonnelLocatorDto.class))
                .list();
        return list;
    }

    @Override
    public List<AreaCateGoryLocationDto> getPersonnelArea(String locationSnList) {
        List<AreaCateGoryLocationDto> list = new ArrayList<>();
        if (locationSnList != null) {
            String[] snList = locationSnList.split(",");
            for (String sn : snList) {
                AreaCateGoryLocationDto areaCateGoryLocationDto = new AreaCateGoryLocationDto();
                PoiLocationEntity locationEntity = poiLocationRepository.getByLocationSn(sn);
                if (locationEntity != null) {
                    areaCateGoryLocationDto.setName(locationEntity.getLocationName());
                    int prisonerCount = getAreaCount(101, locationEntity.getId());
                    int workerCount = getAreaCount(102, locationEntity.getId());
                    int visitorCount = getAreaCount(103, locationEntity.getId());
                    int totalCount = prisonerCount + workerCount + visitorCount;
                    areaCateGoryLocationDto.setPrisonerCount(prisonerCount);
                    areaCateGoryLocationDto.setTotalCount(totalCount);
                    areaCateGoryLocationDto.setWorkerCount(workerCount);
                    areaCateGoryLocationDto.setVisitorCount(visitorCount);
                    list.add(areaCateGoryLocationDto);
                }
            }
        }
        return list;
    }

    private int getAreaCount(int personnelType, Long locationId) {
        List<DictEntity> personnlDictList = dictService.getDictAndSubDictListById(personnelType);
        List<Integer> personnelTypeList = new ArrayList<>();
        for (DictEntity dictEntity : personnlDictList) {
            personnelTypeList.add(dictEntity.getId());
        }
        String sql = "SELECT count(1) FROM v_personnel v" +
                " LEFT JOIN  poi_location  p ON st_contains ( p.location_polygon, v.pos )" +
                " AND p.floor_number = v.floor_number" +
                " WHERE" +
                " v.is_enabled = 1 and p.map_level =3 and p.id =:locationId and v.personnel_type in :personnelType";
        int count = ((Number) entityManager.createNativeQuery(sql).setParameter("personnelType", personnelTypeList).setParameter("locationId", locationId).getSingleResult()).intValue();
        return count;
    }

    //获取异常标签数量详情
    @Override
    public CardStatusDto getCardStatus() {
//        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        CardStatusDto cardStatusDto = new CardStatusDto();
        int lowPowerCount = cardViewRepository.countAllByEnabledAndLowpowerStatus(1, 1);
        int cutOffCount = cardViewRepository.countAllByEnabledAndCutoffStatus(1, 1);
        int offLineCount = cardViewRepository.countAllByEnabledAndOnlineStatus(1, 0);
        int totalCount = cardViewRepository.countAllByEnabled(1);
//        int unNormalCount = lowPowerCount + cutOffCount + offLineCount;
        int normalCoount = getNormalCount();
        int unNormalCount = totalCount - normalCoount;
        cardStatusDto.setNormalCount(normalCoount);
        cardStatusDto.setUnNormalCount(unNormalCount);
        cardStatusDto.setCutoffStatusCount(cutOffCount);
        cardStatusDto.setOfflineStatusCount(offLineCount);
        cardStatusDto.setLowpowerStatusCount(lowPowerCount);
        return cardStatusDto;
    }

    //获取异常标签数量详情--怀化
    @Override
    public CardStatusDto getCardStatusHuaihua(Long orgId) {
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        CardStatusDto cardStatusDto = new CardStatusDto();
        int lowPowerCount = getLowPowerStatus(orgIdList);
        int offLineCount= getoffLineStatus(orgIdList);
        int cutOffCount = getCutOffStatus(orgIdList);
//        int lowPowerCount = cardViewRepository.countAllByEnabledAndLowpowerStatusAndBindStatusAndPersonnelTypeAndOrgIdIn(1, 1,1, 101,orgIdList);
//        int cutOffCount = cardViewRepository.countAllByEnabledAndCutoffStatusAndBindStatusAndPersonnelTypeAndOrgIdIn(1, 1,1, 101, orgIdList);
//        int offLineCount = cardViewRepository.countAllByEnabledAndOnlineStatusAndBindStatusAndPersonnelTypeAndOrgIdIn(1, 0,1, 101, orgIdList);
//        int unNormalCount = lowPowerCount + cutOffCount + offLineCount;
        int normalCoount = getNormalCountHuaihua(orgIdList);
        int totalCount = cardViewRepository.countAllByEnabledAndBindStatusAndOrgIdIn(1,1, orgIdList);
        int unNormalCount = totalCount - normalCoount;
        cardStatusDto.setNormalCount(normalCoount);
        cardStatusDto.setUnNormalCount(unNormalCount);
        cardStatusDto.setCutoffStatusCount(cutOffCount);
        cardStatusDto.setOfflineStatusCount(offLineCount);
        cardStatusDto.setLowpowerStatusCount(lowPowerCount);
        return cardStatusDto;
    }


    private Integer getLowPowerStatus(List<Long> orgIdList){
        String sql = "select count(1) from v_card v left join v_personnel a on a.card_number = v.card_number where v.is_enabled = 1 and  v.personnel_type = 101" +
                " and v.lowpower_status = 1 and v.bind_status = 1 and v.org_id in :orgIdList and a.is_enabled =1";
        int count = ((Number) entityManager.createNativeQuery(sql)
                .setParameter("orgIdList", orgIdList)
                .getSingleResult()).intValue();
        return count;
    }

    private Integer getCutOffStatus(List<Long> orgIdList){
        String sql = "select count(1) from v_card v left join v_personnel a on a.card_number = v.card_number where v.is_enabled = 1 and  v.personnel_type = 101" +
                " and v.cutoff_status = 1 and v.bind_status = 1 and v.org_id in :orgIdList and a.is_enabled =1";
        int count = ((Number) entityManager.createNativeQuery(sql)
                .setParameter("orgIdList", orgIdList)
                .getSingleResult()).intValue();
        return count;
    }

    private Integer getoffLineStatus(List<Long> orgIdList){
        String sql = "select count(1) from v_card v left join v_personnel a on a.card_number = v.card_number where v.is_enabled = 1 and  v.personnel_type = 101" +
                " and v.online_status = 0 and v.bind_status = 1 and v.org_id in :orgIdList and a.is_enabled =1";
        int count = ((Number) entityManager.createNativeQuery(sql)
                .setParameter("orgIdList", orgIdList)
                .getSingleResult()).intValue();
        return count;
    }

    private int getNormalCount() {
        String sql = "select count(1) from v_card where is_enabled = 1 and online_status = 1 " +
                " and (cutoff_status = 0 or cutoff_status is null) and (lowpower_status = 0 or lowpower_status is null)";
        int count = ((Number) entityManager.createNativeQuery(sql).getSingleResult()).intValue();
        return count;
    }

    private int getNormalCountHuaihua(List<Long> orgIdList) {
        String sql = "select count(1) from v_card where is_enabled = 1 and online_status = 1 and  bind_status = 1" +
                " and (cutoff_status = 0 or cutoff_status is null) and (lowpower_status = 0 or lowpower_status is null) and org_id in :orgIdList";
        int count = ((Number) entityManager.createNativeQuery(sql)
                .setParameter("orgIdList", orgIdList)
                .getSingleResult()).intValue();
        return count;
    }

    @Override
    public List<PersonelAllTypeCountDto> getPersonnelTypeCount() {
        List<PersonelAllTypeCountDto> resultList = new ArrayList<>();
        resultList = getTypeCount(resultList, 101);
        resultList = getTypeCount(resultList, 102);
        resultList = getTypeCount(resultList, 103);
        return resultList;
    }

    private List<PersonelAllTypeCountDto> getTypeCount(List<PersonelAllTypeCountDto> resultList, Integer personnelType) {
        List<DictEntity> dictEntityList = dictService.getDictAndSubDictListById(personnelType);
        for (DictEntity dictEntity : dictEntityList) {
            PersonelAllTypeCountDto dto = new PersonelAllTypeCountDto();
            Long count = personnelRepository.countAllByPersonnelTypeInAndEnabled(dictService.getDictAndSubDictIdListById(dictEntity.getId()), 1);
            dto.setCount(count);
            dto.setPersonnelType(dictEntity.getId());
            dto.setPersonnelTypeName(dictEntity.getDictName());
            resultList.add(dto);
        }
        return resultList;
    }

    @Override
    public ZcPersonnelDto getZCPeronnelCount() {
        ZcPersonnelDto zcPersonnelDto = new ZcPersonnelDto();
        zcPersonnelDto.setPrisonerCount(getZcCount(101));
        zcPersonnelDto.setWorkerCount(getZcCount(102));
        zcPersonnelDto.setVisitorCount(getZcCount(103));
        List<PersonelAllTypeCountDetailDto> resultList = new ArrayList<>();
        resultList = getZcCountDetail(resultList, 101);
        resultList = getZcCountDetail(resultList, 102);
        resultList = getZcCountDetail(resultList, 103);
        zcPersonnelDto.setPersonnelTypeCountList(resultList);
        return zcPersonnelDto;
    }

    private int getZcCount(Integer personnlType) {
        List<DictEntity> dictEntityList = dictService.getDictAndSubDictListById(personnlType);
        List<Integer> typeList = new ArrayList<>();
        for (DictEntity dictEntity : dictEntityList) {
            typeList.add(dictEntity.getId());
        }
        return personnelViewRepository.countAllByPersonnelTypeInAndEnabled(typeList, 1);
    }

    private List<PersonelAllTypeCountDetailDto> getZcCountDetail(List<PersonelAllTypeCountDetailDto> resultList, Integer personnelType) {
        List<DictEntity> dictEntityList = dictService.getDictAndSubDictListById(personnelType);
        for (DictEntity dictEntity : dictEntityList) {
            PersonelAllTypeCountDetailDto dto = new PersonelAllTypeCountDetailDto();
            long count = personnelRepository.countAllByPersonnelTypeInAndEnabled(dictService.getDictAndSubDictIdListById(dictEntity.getId()), 1);
            int unbindCount = personnelRepository.countAllByPersonnelTypeInAndEnabledAndCardNumberIsNull(dictService.getDictAndSubDictIdListById(dictEntity.getId()), 1);
            dto.setCount(count);
            dto.setUnbindCount(unbindCount);
            dto.setPersonnelType(dictEntity.getId());
            dto.setPersonnelTypeName(dictEntity.getDictName());
            resultList.add(dto);
        }
        return resultList;
    }

    @Override
    public List<PersonnelOnlineCountDto> getPersonnelOnlineCount() {
        List<PersonnelOnlineCountDto> personnelOnlineCountDtoList = new ArrayList<>();
        List<DictEntity> dictEntityList = dictService.getDictByType(11);
        for (DictEntity dictEntity : dictEntityList) {
            PersonnelOnlineCountDto dto = new PersonnelOnlineCountDto();
            dto.setPersonnelType(dictEntity.getId());
            dto.setPersonnelTypeName(dictEntity.getDictName());
            dto.setOnlineCount(getPersonnelOnlineCount(dictEntity.getId(), 1));
            dto.setOfflineCount(getPersonnelOnlineCount(dictEntity.getId(), 0));
            int totalCount = dto.getOfflineCount() + dto.getOnlineCount();
            dto.setTotalCount(totalCount);
            personnelOnlineCountDtoList.add(dto);

        }
//        personnelOnlineCountDtoList.add(new PersonnelOnlineCountDto(102,getPersonnelOnlineCount(102,1),getPersonnelOnlineCount(102,0)));
//        personnelOnlineCountDtoList.add(new PersonnelOnlineCountDto(103,getPersonnelOnlineCount(103,1),getPersonnelOnlineCount(103,0)));
        return personnelOnlineCountDtoList;
    }

    private int getPersonnelOnlineCount(Integer personnlType, Integer onlineStatu) {
        List<DictEntity> dictEntityList = dictService.getDictAndSubDictListById(personnlType);
        List<Integer> typeList = new ArrayList<>();
        for (DictEntity dictEntity : dictEntityList) {
            typeList.add(dictEntity.getId());
        }
        return personnelViewRepository.countAllByPersonnelTypeInAndEnabledAndOnlineStatus(typeList, 1, onlineStatu);
    }

    @Override
    public PersonnelCountByLocationTotalDto getPersonnelCountByLocator(Integer personnelType, Integer locationId, Long orgId) {
        PersonnelCountByLocationTotalDto totalDto = new PersonnelCountByLocationTotalDto();
        List<PersonnelCountByLocationDto> countByLocationDtoList = new ArrayList<>();
        List<DictEntity> personnlDictList = dictService.getDictAndSubDictListById(personnelType);
        List<Integer> personnelTypeList = new ArrayList<>();
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        for (DictEntity dictEntity : personnlDictList) {
            personnelTypeList.add(dictEntity.getId());
        }
        if (locationId != null && locationId == 111111) {
            return getUntreatedCount(personnelTypeList, orgIdList);
        }
        List<DictEntity> locationDictList = new ArrayList<>();
        if (locationId != null) {
            locationDictList = dictService.getDictAndSubDictListById(locationId);
        } else {
            locationDictList = dictService.getDictByType(31);
        }
        for (DictEntity dictEntity : locationDictList) {
            List<DictEntity> locationDictChildList = dictService.getDictAndSubDictListById(dictEntity.getId());
            List<Integer> locationTypeList = new ArrayList<>();
            for (DictEntity child : locationDictChildList) {
                locationTypeList.add(child.getId());
            }
            int count = getCount(personnelTypeList, locationTypeList, orgIdList);
            countByLocationDtoList.add(new PersonnelCountByLocationDto(dictEntity.getId(), dictEntity.getDictName(), count));
        }
        int totalCount = personnelViewRepository.countAllByEnabledAndPosIsNotNullAndOrgIdInAndOnlineStatus(1, orgIdList, 1);
        totalDto.setTotal(totalCount);
        totalDto.setLocationDtoList(countByLocationDtoList);
        return totalDto;
    }

    //获取未知区域人数
    private PersonnelCountByLocationTotalDto getUntreatedCount(List<Integer> personnelTypeList, List<Long> orgIdList) {
        PersonnelCountByLocationTotalDto totalDto = new PersonnelCountByLocationTotalDto();
        List<PersonnelCountByLocationDto> countByLocationDtoList = new ArrayList<>();
        List<DictEntity> locationDictList = dictService.getDictByType(31);
        List<Integer> allLocationListType = new ArrayList<>();
        for (DictEntity entity : locationDictList) {
            allLocationListType.add(entity.getId());
        }
        int treatedCount = getCount(personnelTypeList, allLocationListType, orgIdList);
        int totalCount = personnelViewRepository.countAllByEnabledAndPosIsNotNullAndOrgIdInAndOnlineStatus(1, orgIdList, 1);
        int unTreatedCount = totalCount - treatedCount;
        countByLocationDtoList.add(new PersonnelCountByLocationDto(111111, "未知区域", unTreatedCount));
        totalDto.setTotal(totalCount);
        totalDto.setLocationDtoList(countByLocationDtoList);
        return totalDto;
    }

    private int getCount(List<Integer> personnelType, List<Integer> locationTypeList, List<Long> orgIdList) {
        String sql = "SELECT  count(1) " +
                " FROM v_personnel v" +
                " LEFT JOIN poi_location p ON st_contains ( p.location_polygon, v.pos )" +
                " AND p.floor_number = v.floor_number" +
                " WHERE" +
                " v.is_enabled = 1 and v.pos is not null and p.map_level =3 AND p.location_Type in :locationTypeList  and org_id in :orgIdList and online_status = 1 ";
        if (personnelType.size() > 0) {
            sql = sql + " and v.personnel_Type in :personnelType";
            return ((Number) entityManager.createNativeQuery(sql)
                    .setParameter("orgIdList", orgIdList)
                    .setParameter("personnelType", personnelType)
                    .setParameter("locationTypeList", locationTypeList)
                    .getSingleResult()).intValue();
        } else {
            return ((Number) entityManager.createNativeQuery(sql)
                    .setParameter("orgIdList", orgIdList)
                    .setParameter("locationTypeList", locationTypeList)
                    .getSingleResult()).intValue();
        }
    }

    @Override
    public PersonnelCountByLocationTotalDto getPersonnelCountByLocator2(Integer personnelType,
                                                                        Integer locationTypeId, Long orgId) {
        List<PersonnelCountByLocationDto> list = new ArrayList<>();
        List<DictEntity> locationDictList = new ArrayList<>();
        if (locationTypeId != null) {
            if (locationTypeId.equals(111111)) {
                List<PersonnelLocationDto> personnelList = getPersonnelLocationByTypeAndOrg(personnelType, orgId);
                list.add(new PersonnelCountByLocationDto(111111, "未知区域",
                        (int) personnelList.stream()
                                .filter(personnelLocationDto -> personnelLocationDto.getLocationId() == null)
                                .count()));
                return new PersonnelCountByLocationTotalDto(personnelList.size(), list);
            } else {
                locationDictList = dictService.getDictAndSubDictListById(locationTypeId);
            }
        } else {
            locationDictList = dictService.getDictByType(31);
        }
        List<PersonnelLocationDto> personnelList = getPersonnelLocationByTypeAndOrg(personnelType, orgId);
        for (DictEntity dict : locationDictList) {
            List<Integer> locationTypeList = dictService.getDictAndSubDictIdListById(dict.getId());
            int count = (int) getCount(personnelList, locationTypeList);
            list.add(new PersonnelCountByLocationDto(dict.getId(), dict.getDictName(), count));
        }
        return new PersonnelCountByLocationTotalDto(personnelList.size(), list);
    }

    private long getCount(List<PersonnelLocationDto> personnelList, List<Integer> locationTypeList) {
        return personnelList.stream()
                .filter(personnelLocationDto -> personnelLocationDto.getLocationType() != null
                        && locationTypeList.contains(personnelLocationDto.getLocationType()))
                .count();
    }

    private List<PersonnelLocationDto> getPersonnelLocationByTypeAndOrg(Integer personnelType, Long orgId) {
        String sql = "select * from v_personnel v where v.is_enabled =1 and v.online_status = 1 and v.pos is not null ";
        List<Integer> personnelTypeList = dictService.getDictAndSubDictIdListById(personnelType);
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        if (personnelTypeList != null && personnelTypeList.size() > 0) {
            sql += " and v.personnel_type in :personnelTypeList and v.org_id in :orgIdList ";
            List<PersonnelView> list = entityManager.createNativeQuery(sql, PersonnelView.class)
                    .setParameter("personnelTypeList", personnelTypeList)
                    .setParameter("orgIdList", orgIdList)
                    .getResultList();
            return getPersonnelLocationList(list);
        } else {
            sql += " and v.org_id in :orgIdList ";
            List<PersonnelView> list = entityManager.createNativeQuery(sql, PersonnelView.class)
                    .setParameter("orgIdList", orgIdList)
                    .getResultList();
            return getPersonnelLocationList(list);
        }
    }

    private List<PersonnelLocationDto> getPersonnelLocationList(List<PersonnelView> list) {
        List<PoiLocationEntity> locationList = poiLocationRepository.getAllByMapLevel(3);
        return list.stream().map(personnelView -> {
            Optional<PoiLocationEntity> location3 = locationList.stream()
                    .filter(location -> location.getFloorNumber().equals(personnelView.getFloorNumber())
                            && location.getLocationPolygon().contains(personnelView.getPos()))
                    .findFirst();
            if (location3.isPresent()) {
                return new PersonnelLocationDto(personnelView.getId(), personnelView.getPersonnelName(),
                        personnelView.getPersonnelType(), personnelView.getOrgId(),
                        location3.get().getId(), location3.get().getLocationName(), location3.get().getLocationType());
            } else {
                return new PersonnelLocationDto(personnelView.getId(), personnelView.getPersonnelName(),
                        personnelView.getPersonnelType(), personnelView.getOrgId(), null, null, null);
            }
        }).collect(Collectors.toList());
    }

    @Override
    public Long getFocusedPrisonerCount(Long orgId) {
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        return personnelPrisonerViewRepository.countAllByEnabledAndIsFocusedPrisonerAndOrgIdIn(1, 1, orgIdList);
    }

    @Override
    public List<PersonnelCountByOrgDto> getPersonnelCountByOrg(Long orgId) {
        List<PersonnelCountByOrgDto> personnelCountByOrgDtoList = new ArrayList<>();
        if (orgService.getOrgInfoById(orgId) != null) {
            List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
            personnelCountByOrgDtoList.add(new PersonnelCountByOrgDto(orgId, orgService.getOrgInfoById(orgId).getOrgName(),
                    personnelViewRepository.countAllByEnabledAndOrgIdIn(1, orgIdList)));
            List<OrgEntity> orgEntityList = orgService.getSubOrgList(orgId);
            for (OrgEntity orgEntity : orgEntityList) {
                PersonnelCountByOrgDto personnelCountByOrgDto = new PersonnelCountByOrgDto();
                List<Long> childOrgList = orgService.getSubOrgIdList(orgEntity.getId());
                int count = personnelViewRepository.countAllByEnabledAndOrgIdIn(1, childOrgList);
                personnelCountByOrgDto.setOrgId(orgEntity.getId());
                personnelCountByOrgDto.setOrgName(orgEntity.getOrgName());
                personnelCountByOrgDto.setCount(count);
                personnelCountByOrgDtoList.add(personnelCountByOrgDto);
            }
        }

        return personnelCountByOrgDtoList;
    }

    @Override
    public List<PersonnelCountDetailByOrgDto> getPersonnelCountDetailByOrg(Long orgId) {
        List<PersonnelCountDetailByOrgDto> personnelCountDetailByOrgDtos = new ArrayList<>();
        if (orgService.getOrgInfoById(orgId) != null) {
            List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
            personnelCountDetailByOrgDtos.add(new PersonnelCountDetailByOrgDto(orgId, orgService.getOrgInfoById(orgId).getOrgName(),
                    personnelViewRepository.countAllByEnabledAndOrgIdInAndOnlineStatus(1,orgIdList,1),
                    personnelViewRepository.countAllByEnabledAndOrgIdInAndOnlineStatus(1,orgIdList,0),
                    personnelViewRepository.countAllByEnabledAndOrgIdIn(1, orgIdList)));

            List<OrgEntity> orgEntityList = orgService.getSubOrgList(orgId);
            for (OrgEntity orgEntity : orgEntityList) {
                PersonnelCountDetailByOrgDto personnelCountDetailByOrgDto = new PersonnelCountDetailByOrgDto();
                List<Long> childOrgList = orgService.getSubOrgIdList(orgEntity.getId());
                int zcCount = personnelViewRepository.countAllByEnabledAndOrgIdIn(1, childOrgList);
                personnelCountDetailByOrgDto.setOrgId(orgEntity.getId());
                personnelCountDetailByOrgDto.setOrgName(orgEntity.getOrgName());
                personnelCountDetailByOrgDto.setOnlineCount(personnelViewRepository.countAllByEnabledAndOrgIdInAndOnlineStatus(1,childOrgList,1));
                personnelCountDetailByOrgDto.setOfflineCount(personnelViewRepository.countAllByEnabledAndOrgIdInAndOnlineStatus(1,childOrgList,0));
                personnelCountDetailByOrgDto.setZcCount(zcCount);
                personnelCountDetailByOrgDtos.add(personnelCountDetailByOrgDto);
            }
        }
        return personnelCountDetailByOrgDtos;
    }

    @Override
    public Page<PersonnelCountByLocationTypeDto> getPersonnelCountByLocationType(int locationType, Long orgId, Pageable pageable) {
        List<PersonnelCountByLocationTypeDto> dtoList = new ArrayList<>();
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        if (locationType == 999) {
            String[] strList = appLocationType.split(",");
            List<Integer> locationTypeList = new ArrayList<>();
//            locationTypeList.add(301);
            for (String str : strList) {
                locationTypeList.add(Integer.valueOf(str));
            }
//            List<PoiLocationEntity> poiLocationEntityList = poiLocationRepository.getAllByLocationTypeNotIn(locationTypeList);
            List<PoiLocationEntity> poiLocationEntityList = poiLocationRepository.getAllByLocationTypeNotInAndMapLevel(locationTypeList,Constants.MAP_LEVEL.THREE.id);
            for (PoiLocationEntity poiLocationEntity : poiLocationEntityList) {
                dtoList.add(new PersonnelCountByLocationTypeDto(poiLocationEntity.getId(),
                        poiLocationEntity.getLocationName(), getPerosnnelCountByLocation(poiLocationEntity.getId(), orgIdList)));
            }
        } else {
//            poiLocationRepository.getAllByLocationType(locationType);
            List<PoiLocationEntity> poiLocationEntityList = poiLocationRepository.getAllByLocationTypeAndMapLevel(locationType, Constants.MAP_LEVEL.THREE.id);
            for (PoiLocationEntity poiLocationEntity : poiLocationEntityList) {
                dtoList.add(new PersonnelCountByLocationTypeDto(poiLocationEntity.getId(),
                        poiLocationEntity.getLocationName(), getPerosnnelCountByLocation(poiLocationEntity.getId(), orgIdList)));
            }
        }
        return new PageImpl<>(new ArrayList<>(dtoList), pageable, dtoList.size());
    }

    private int getPerosnnelCountByLocation(Long locationId, List<Long> orgIdList) {
        String sql = "SELECT count(1) FROM v_personnel v" +
                " LEFT JOIN poi_location p ON st_contains ( p.location_polygon, v.pos )" +
                " AND p.floor_number = v.floor_number" +
                " WHERE" +
                " v.is_enabled = 1 and p.map_level =3 and org_id in :orgIdList and p.id = :locationId";

        int count = ((Number) entityManager.createNativeQuery(sql).setParameter("locationId", locationId).setParameter("orgIdList", orgIdList).getSingleResult()).intValue();
        return count;
    }


    @Override
    public PersonnelCountForAppDtp getAppHomePageData(Long orgId, Long notifyPersonnelId) {
        PersonnelCountForAppDtp personnelCountForAppDtp = new PersonnelCountForAppDtp();
        if (orgService.getOrgInfoById(orgId) != null) {
            List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
            int zcPersonnelCount = personnelViewRepository.countAllByEnabledAndOrgIdIn(1, orgIdList);
            //在押定位人员总数
            int totalCount = personnelViewRepository.countAllByEnabledAndPersonnelTypeAndPosIsNotNullAndOrgIdIn(1, 101,orgIdList);
            int lowPower = alarmNotifyRecordVeiwRepository.countAllByNotifyPersonnelIdAndAlarmTypeAndClosed(notifyPersonnelId, 408, 0);
            int cutOff = alarmNotifyRecordVeiwRepository.countAllByNotifyPersonnelIdAndAlarmTypeAndClosed(notifyPersonnelId, 407, 0);
            int call = alarmNotifyRecordVeiwRepository.countAllByNotifyPersonnelIdAndAlarmTypeAndRead(notifyPersonnelId, 406, 0);
            int offLine = alarmNotifyRecordVeiwRepository.countAllByNotifyPersonnelIdAndAlarmTypeAndClosed(notifyPersonnelId, 409, 0);
            List<Integer> alarmTypeList = new ArrayList<>();
            List<DictEntity> dictEntityList = dictService.getGuardTypes();
            for (DictEntity dictEntity : dictEntityList) {
                alarmTypeList.add(dictEntity.getId());
            }
//            int violation = alarmNotifyRecordVeiwRepository.countAllByNotifyPersonnelIdAndAlarmTypeInAndClosed(notifyPersonnelId, alarmTypeList, 0);
            int violation = alarmNotifyRecordVeiwRepository.countAllByNotifyPersonnelIdAndAlarmTypeInAndRead(notifyPersonnelId, alarmTypeList, 0);

            int lowPowerCount = cardViewRepository.countAllByEnabledAndLowpowerStatusAndBindStatusAndPersonnelTypeAndOrgIdIn(1, 1,1, 101,orgIdList);
            int cutOffCount = cardViewRepository.countAllByEnabledAndCutoffStatusAndBindStatusAndPersonnelTypeAndOrgIdIn(1, 1,1, 101,orgIdList);
            int offLineCount = cardViewRepository.countAllByEnabledAndOnlineStatusAndBindStatusAndPersonnelTypeAndOrgIdIn(1, 0,1, 101,orgIdList);

            personnelCountForAppDtp.setLowPowerCount(lowPowerCount);
            personnelCountForAppDtp.setCutoffCount(cutOffCount);
            personnelCountForAppDtp.setOffLineCount(offLineCount);

            personnelCountForAppDtp.setZcPersonnelCount(zcPersonnelCount);
            personnelCountForAppDtp.setJqPersonnelCount(totalCount);
//            personnelCountForAppDtp.setLowPowerCount(lowPower);
            personnelCountForAppDtp.setCallCount(0);
//            personnelCountForAppDtp.setCutoffCount(cutOff);
//            personnelCountForAppDtp.setOffLineCount(offLine);
            personnelCountForAppDtp.setViolationCount(violation);
//            personnelCountForAppDtp.setPersonnelCountByLocationTypeDtoList(getPersonnelCountByArea(orgId, totalCount));
        }
        return personnelCountForAppDtp;
    }

    @Override
    public List<PersonnelCountByLocationTypeDto> getCountByLocationType(Long orgId){
        List<PersonnelCountByLocationTypeDto> personnelCountByLocationTypeDtoList = new ArrayList<>();
        if (orgService.getOrgInfoById(orgId) != null) {
            List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
            //在押定位人员总数
            int totalCount = personnelViewRepository.countAllByEnabledAndPersonnelTypeAndPosIsNotNullAndOrgIdIn(1, 101, orgIdList);
            personnelCountByLocationTypeDtoList = getPersonnelCountByArea(orgId, totalCount);
        }
        return personnelCountByLocationTypeDtoList;
    }

    private List<PersonnelCountByLocationTypeDto> getPersonnelCountByArea(Long orgId, int totalCount) {
        List<PersonnelCountByLocationTypeDto> personnelCountByLocationTypeDtoList = new ArrayList<>();
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        List<Integer> typeList = new ArrayList<>();
        String[] locatinoTypeList = appLocationType.split(",");
        for (String locationType : locatinoTypeList) {
            PersonnelCountByLocationTypeDto dto = new PersonnelCountByLocationTypeDto();
            List<Integer> locationDictList = new ArrayList<>();
            List<Integer> personnelTypeList = new ArrayList<>();
            locationDictList.add(Integer.valueOf(locationType));
            typeList.add(Integer.valueOf(locationType));
            personnelTypeList.add(101);
            int count = getCount(personnelTypeList, locationDictList, orgIdList);
            totalCount = totalCount - count;
            DictEntity dictEntity = dictService.getDict(Integer.valueOf(locationType));
            dto.setLcaotionId(Long.valueOf(locationType));
            dto.setName(dictEntity.getDictName());
            dto.setCount(count);
            personnelCountByLocationTypeDtoList.add(dto);
        }
        List<Integer> appTypeList = new ArrayList<>();
        for (String locationType1 : locatinoTypeList){
            appTypeList.add(Integer.valueOf(locationType1));
        }

        Integer qtCount = getAppQtCount(101, appTypeList, orgIdList);
        personnelCountByLocationTypeDtoList.add(new PersonnelCountByLocationTypeDto(999l, "其他", qtCount));
        return personnelCountByLocationTypeDtoList;
    }

    private int getAppQtCount(Integer personnelType, List<Integer> locationTypeList, List<Long> orgIdList) {
        String sql = "SELECT  count(1) " +
                " FROM v_personnel v" +
                " LEFT JOIN poi_location p ON st_contains ( p.location_polygon, v.pos )" +
                " AND p.floor_number = v.floor_number" +
                " WHERE" +
                " v.is_enabled = 1 and v.pos is not null and p.map_level =3 AND p.location_Type not in :locationTypeList  and org_id in :orgIdList";
            sql = sql + " and v.personnel_Type = :personnelType";
            return ((Number) entityManager.createNativeQuery(sql).setParameter("orgIdList", orgIdList).setParameter("personnelType", personnelType).setParameter("locationTypeList", locationTypeList).getSingleResult()).intValue();
    }


    @Override
    public Page<LocationPersonnelDetailForAppDto> getActualPersonnelsInfoInLocation(Long locationId, Long orgId, Pageable pageable) {
        PoiLocationEntity locationEntity = poiLocationRepository.getById(locationId);
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        String sql = "select p.id as personnel_id, p.personnel_name,p.id_number,p.gender, p.card_number, p.personnel_sn, p.org_id, p.org_name, t.location_name " +
                " from v_personnel p " +
                " left join (select * from poi_location where map_level = 3 and floor_number = :fnumber) t on st_contains(t.location_polygon, p.pos) " +
                " where p.is_enabled = 1 and p.org_id in :orgIdList and p.floor_number = :fnumber and st_contains(:pg, p.pos)";
        List<LocationPersonnelDetailForAppDto> content = entityManager.createNativeQuery(sql, LocationPersonnelDetailForAppDto.class)
                .setParameter("orgIdList", orgIdList)
                .setParameter("fnumber", locationEntity.getFloorNumber())
                .setParameter("pg", locationEntity.getLocationPolygon())
                .getResultList();
        return new PageImpl<>(new ArrayList<>(content), pageable, content.size());
    }

    @Override
    public List<PersonnelDetailByOrgDto> getPersonnelDetailByOrg(Long orgId) {
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        String sql = "select t.id as personnel_Id,t.personnel_name,t.card_number,a.unifo_no," +
                "t.online_status,t.hex_number from (v_personnel t LEFT JOIN personnel_prisoner a ON ((t.id = a.personnel_id)))  " +
                "where t.org_id in :orgIdList and t.is_enabled =1 ";
        List<PersonnelDetailByOrgDto> personnelList = entityManager.createNativeQuery(sql, PersonnelDetailByOrgDto.class)
                .setParameter("orgIdList", orgIdList)
                .getResultList();
        return personnelList;
    }

    @Override
    public String getAppVersion(){
        return appVersion;
    }

    @Override
    public Page<LocationPersonnelDetailForAppDto> getPersonnelDetailByLocatorType(int locationType, Long orgId, Pageable pageable) {
        List<PersonnelCountByLocationTypeDto> dtoList = new ArrayList<>();
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        if (locationType == 999) {
            String[] strList = appLocationType.split(",");
            List<Integer> locationTypeList = new ArrayList<>();
            for (String str : strList) {
                locationTypeList.add(Integer.valueOf(str));
            }
            String sql = "SELECT  v.id as personnel_id, v.personnel_name,v.id_number,v.gender, v.card_number, v.personnel_sn, v.org_id, v.org_name, p.location_name " +
                    " FROM v_personnel v" +
                    " LEFT JOIN poi_location p ON st_contains ( p.location_polygon, v.pos )" +
                    " AND p.floor_number = v.floor_number" +
                    " WHERE" +
                    " v.is_enabled = 1 and p.map_level =3 and org_id in :orgIdList and p.location_type not in :locationTypeList";
            List<LocationPersonnelDetailForAppDto> content = entityManager.createNativeQuery(sql, LocationPersonnelDetailForAppDto.class)
                    .setParameter("orgIdList", orgIdList)
                    .setParameter("locationTypeList", locationTypeList)
                    .getResultList();
            return new PageImpl<>(new ArrayList<>(content), pageable, content.size());
        } else {
            String sql = "SELECT  v.id as personnel_id, v.personnel_name,v.id_number,v.gender, v.card_number, v.personnel_sn, v.org_id, v.org_name, p.location_name " +
                    " FROM v_personnel v" +
                    " LEFT JOIN poi_location p ON st_contains ( p.location_polygon, v.pos )" +
                    " AND p.floor_number = v.floor_number" +
                    " WHERE" +
                    " v.is_enabled = 1 and p.map_level =3 and org_id in :orgIdList and p.location_type = :locationType";
            List<LocationPersonnelDetailForAppDto> content = entityManager.createNativeQuery(sql, LocationPersonnelDetailForAppDto.class)
                    .setParameter("orgIdList", orgIdList)
                    .setParameter("locationType", locationType)
                    .getResultList();
            return new PageImpl<>(new ArrayList<>(content), pageable, content.size());
        }
    }

    @Override
    public Page<LocationPersonnelDetailForAppDto> getPersonnelForApp(String personnelName, Long cardNumber, Long orgId, Pageable pageable){
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        String sql = "SELECT  v.id as personnel_id, v.personnel_name,v.id_number,v.gender, v.card_number, v.personnel_sn, v.org_id, v.org_name, p.location_name " +
                " FROM v_personnel v" +
                " LEFT JOIN poi_location p ON st_contains ( p.location_polygon, v.pos )" +
                " AND p.floor_number = v.floor_number" +
                " WHERE" +
                " v.is_enabled = 1 and p.map_level =3 and org_id in :orgIdList ";
        List<LocationPersonnelDetailForAppDto> content = new ArrayList<>();
        if(personnelName != null){
            sql = sql + " and v.personnel_name like '%" + personnelName + "%' ";
        }
        if(cardNumber != null){
            sql = sql + " and v.card_number like '%" + cardNumber + "%' ";
        }
        content = entityManager.createNativeQuery(sql, LocationPersonnelDetailForAppDto.class)
                .setParameter("orgIdList", orgIdList)
                .getResultList();
        return new PageImpl<>(new ArrayList<>(content), pageable, content.size());
    }

    @Override
    public List<UntreatedPersonDetailDto> getUntreatedPersonDetail(Long orgId) {
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        String sql = "SELECT  v.id as personnel_id, v.personnel_name,v.id_number, v.card_number, v.org_id, v.org_name, v.hex_number " +
                " FROM v_personnel v" +
                " WHERE" +
                " v.is_enabled = 1  and  (pos is null or online_status = 0) and org_id in :orgIdList ";
        List<UntreatedPersonDetailDto> content =  entityManager.createNativeQuery(sql, UntreatedPersonDetailDto.class)
                .setParameter("orgIdList", orgIdList)
                .getResultList();
        return content;
    }

    @Override
    public List<LocationPersonnelDetailDto> getPersonDetail(Long orgId, String typeList, Integer flag) {
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        List<LocationPersonnelDetailDto> content = new ArrayList<>();
        String[] list = typeList.split(",");
        if(flag == 0){
            DictEntity dictEntity = dictRepository.getByDictNameAndTypeId(list[0], 31);
            List<DictEntity> locationTypeList = dictService.getDictAndSubDictListById(dictEntity.getId());
            List<Integer> typeIdList = new ArrayList<>();
            for(DictEntity dictEntity1 : locationTypeList){
                typeIdList.add(dictEntity1.getId());
            }
            String sql = "SELECT  v.id as personnel_id, v.personnel_name,v.id_number,v.gender, v.card_number, " +
                    "v.personnel_sn, v.org_id, v.org_name, p.id as location_id, p.location_type, p.location_name, d.unifo_no, v.hex_number " +
                    " FROM v_personnel v" +
                    " left join v_personnel_prisoner d on d.id = v.id " +
                    " left join poi_location p ON st_contains ( p.location_polygon, v.pos )" +
                    " AND p.floor_number = v.floor_number" +
                    " WHERE" +
                    " v.is_enabled = 1 and p.map_level =3 and v.org_id in :orgIdList and p.location_type in :typeIdList and v.online_status = 1";
           content = entityManager.createNativeQuery(sql, LocationPersonnelDetailDto.class)
                    .setParameter("orgIdList", orgIdList)
                    .setParameter("typeIdList", typeIdList)
                    .getResultList();
        } else if(flag == 1) {
            List<Integer> idList = new ArrayList<>();
            for (String typeName : list) {
                DictEntity dictEntity = dictRepository.getByDictNameAndTypeId(typeName, 31);
                List<DictEntity> dictEntityList = dictRepository.getAllByPid(dictEntity.getId());
                for (DictEntity dictEntity1 : dictEntityList) {
                    idList.add(dictEntity1.getId());
                }
                idList.add(dictEntity.getId());
            }
            String sql = "SELECT  v.id as personnel_id, v.personnel_name,v.id_number,v.gender, v.card_number, " +
                    "v.personnel_sn, v.org_id, v.org_name, p.id as location_id, p.location_type, p.location_name, d.unifo_no, v.hex_number " +
                    " FROM v_personnel v" +
                    " left join v_personnel_prisoner d on d.id = v.id " +
                    " LEFT JOIN poi_location p ON st_contains ( p.location_polygon, v.pos )" +
                    " AND p.floor_number = v.floor_number" +
                    " WHERE" +
                    " v.is_enabled = 1 and p.map_level =3 and v.org_id in :orgIdList and p.location_type not in :idList and v.online_status = 1";
            content = entityManager.createNativeQuery(sql, LocationPersonnelDetailDto.class)
                    .setParameter("orgIdList", orgIdList)
                    .setParameter("idList", idList)
                    .getResultList();
        }
        return content;

    }

    @Override
    public List<LocationPersonnelDetailDto> getPersonDetail2(Long orgId, String locationTypeNames, Integer flag) {
        List<Long> orgIdList = orgService.getSubOrgIdList(orgId);
        List<Integer> locationTypeList = getLocationTypeList(locationTypeNames);
        if (orgIdList != null && orgIdList.size() > 0
                && locationTypeList != null && locationTypeList.size() > 0) {
            String sql = "select * from v_personnel_prisoner v where v.is_enabled = 1 and v.online_status = 1 " +
                    " and v.org_id in :orgIdList ";
            //and v.pos is not null
            List<PersonnelPrisonerView> personnelList =
                    entityManager.createNativeQuery(sql, PersonnelPrisonerView.class)
                            .setParameter("orgIdList", orgIdList)
                            .getResultList();
            return getPersonnelDetailLocationList(personnelList, flag, locationTypeList);
        }
        return new ArrayList<>();
    }

    private List<LocationPersonnelDetailDto> getPersonnelDetailLocationList(List<PersonnelPrisonerView> list,
                                                                            Integer flag, List<Integer> locationTypeList) {
        List<PoiLocationEntity> locationList = poiLocationRepository.getAllByMapLevel(3);
        return list.stream()
                .map(personnel -> {
                    Optional<PoiLocationEntity> location3 = locationList.stream()
                            .filter(location -> location.getFloorNumber().equals(personnel.getFloorNumber())
                                    && location.getLocationPolygon().contains(personnel.getPos()))
                            .findFirst();
                    if (location3.isPresent()) {
                        return new LocationPersonnelDetailDto(personnel.getId(), personnel.getPersonnelName(),
                                personnel.getIdNumber(), personnel.getGender(), personnel.getCardNumber(),
                                personnel.getPersonnelSn(), personnel.getOrgId(), personnel.getOrgName(),
                                location3.get().getId(), location3.get().getLocationType(), location3.get().getLocationName(),
                                personnel.getUnifoNo(), personnel.getHexNumber());
                    } else {
                        return new LocationPersonnelDetailDto(personnel.getId(), personnel.getPersonnelName(),
                                personnel.getIdNumber(), personnel.getGender(), personnel.getCardNumber(),
                                personnel.getPersonnelSn(), personnel.getOrgId(), personnel.getOrgName(),
                                null, null, null,
                                personnel.getUnifoNo(), personnel.getHexNumber());
                    }
                }).filter(dto -> {
                    if (flag == 0) {
                        return dto.getLocationType() != null &&  locationTypeList.contains(dto.getLocationType());
                    } else {
                        return dto.getLocationType() == null || (!locationTypeList.contains(dto.getLocationType()));
                    }
                }).collect(Collectors.toList());
    }

    private List<Integer> getLocationTypeList(String locationTypeNames) {
        String[] typeNameList = locationTypeNames.split(",");
        List<Integer> typeList = new ArrayList<>();
        for (String typeName : typeNameList) {
            DictEntity dictEntity = dictRepository.getByDictNameAndTypeId(typeName, 31);
            if (dictEntity != null) {
                typeList.addAll(dictService.getDictAndSubDictIdListById(dictEntity.getId()));
            }
        }
        return typeList;
    }
}
